package io;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;

/**
 * dump data from database and writes into files in a pre-defined format.
 * 
 * @author xiaohui
 * 
 */
public class DataDumper {

	// random generator for usage
	// generate num usages and usage from 1 - 5
	static long seed = 3892371;
	static Random rand = new Random(seed);
	static int range = 5;

	// public static void dumpFacility(Connection conn, String poiTable,
	// String mmTable, String outFile) throws Exception {
	// String sql =
	// "select a.id, a.mpx, a.mpy, b.seg_id, b.offset_from_beginning from "
	// + poiTable + " a, " + mmTable + " b where a.id = b.poi_id";
	// System.out.println(sql);
	//
	// Statement st = conn.createStatement();
	// ResultSet rs = st.executeQuery(sql);
	// BufferedWriter bw = new BufferedWriter(new FileWriter(outFile));
	//
	// int numLines = 0;
	// while (rs.next()) {
	// bw.write(rs.getInt(1) + " " + rs.getInt(2) + " " + rs.getInt(3)
	// + " " + rs.getInt(4) + " " + rs.getDouble(5));
	// bw.newLine();
	// numLines++;
	// }
	//
	// bw.close();
	// System.out.println("Facility: " + numLines + " written.");
	// }

	/**
	 * 
	 * @param conn
	 * @param poiTable
	 * @param mmTable
	 * @param outFile
	 * @throws Exception
	 */
	public static void dumpRoutes(Connection conn, String routeTable,
			String outFile) throws Exception {

		String aggSql = "select routeid, count(*) from " + routeTable
				+ " group by routeid;";
		Statement st = conn.createStatement();
		ResultSet rs = st.executeQuery(aggSql);

		Map<Integer, Integer> id2num = new HashMap<Integer, Integer>();
		while (rs.next()) {
			id2num.put(rs.getInt("routeid"), rs.getInt(2));
		}

		String sql = "select routeid, xpos, ypos, edgeid, "
				+ "ST_X(point_utm) as mx, ST_Y(point_utm) as my, ratiofromnode "
				+ "from " + routeTable + " order by routeid, id";

		System.out.println(sql);
		ResultSet rs1 = st.executeQuery(sql);
		BufferedWriter bw = new BufferedWriter(new FileWriter(outFile));

		int numLines = 0;
		int currRid = -1;
		String lastLine = "";

		while (rs1.next()) {
			System.out.println("routeid:" + rs1.getInt(1));

			int routeid = rs1.getInt("routeid");
			if (currRid != routeid) {
				// begin a new block - a new route
				currRid = routeid;
				// write route header
				int numpts = id2num.get(routeid);
				System.out.println("numpts:" + numpts);
				int numUsage = rand.nextInt(range) + 1;
				String line1 = routeid + "," + numpts + "," + numUsage;
				bw.write(line1);
				bw.newLine();

				if (lastLine.length() == 0) {
					for (int i = 0; i < numpts; i++) {
						System.out.println("writing node...");
						// write first route node
						String nodeline = rs1.getInt("xpos") + ","
								+ rs1.getInt("ypos") + ","
								+ rs1.getInt("edgeid") + ","
								+ rs1.getInt("mx") + ","
								+ rs1.getInt("my") + ","
								+ rs1.getDouble("ratiofromnode");
						bw.write(nodeline);
						bw.newLine();
						rs1.next();
					}
					lastLine = rs1.getInt("xpos") + ","
							+ rs1.getInt("ypos") + ","
							+ rs1.getInt("edgeid") + ","
							+ rs1.getInt("mx") + ","
							+ rs1.getInt("my") + ","
							+ rs1.getDouble("ratiofromnode");
				} else {
					// write first node which is read from last run
					bw.write(lastLine);
					bw.newLine();

					for (int i = 0; i < numpts - 1; i++) {
						System.out.println("writing node...");
						// write first route node
						String nodeline = rs1.getInt("xpos") + ","
								+ rs1.getInt("ypos") + ","
								+ rs1.getInt("edgeid") + ","
								+ rs1.getInt("mx") + ","
								+ rs1.getInt("my") + ","
								+ rs1.getDouble("ratiofromnode");
						bw.write(nodeline);
						bw.newLine();
						rs1.next();
					}

					if (!rs1.isAfterLast()) {
						lastLine =rs1.getInt("xpos") + ","
								+ rs1.getInt("ypos") + ","
								+ rs1.getInt("edgeid") + ","
								+ rs1.getInt("mx") + ","
								+ rs1.getInt("my") + ","
								+ rs1.getDouble("ratiofromnode");
					}
				}

				// write usage
				for (int i = 0; i < numUsage; i++) {
					String s = (rand.nextInt(range) + 1) + "";
					bw.write(s);
					bw.newLine();
				}

			}
			numLines++;
		}

		rs.close();
		rs1.close();
		bw.close();
		System.out.println("Routes: " + numLines + " written.");
	}

	public static void main(String[] args) throws Exception {
		Database db = new Database();
		Connection conn = db.getConnection();

		String poiTable = "maxseg_poi";
		String mmTable = "maxseg_poi_seg";
//		String outFile = "D:/jung/routes_dbdump.txt";
		String outFile ="D:/jung/routes_dbdump.csv"; 
		String routeTable = "maxseg_routes";

		// dumpFacility(conn, poiTable, mmTable, outFile);
		dumpRoutes(conn, routeTable, outFile);

		db.closeConnection();
	}
}
